<?php
if (!defined('APP_ID')) { die('No direct script access allowed'); }

/**
 * Category model
 */
class Document {

	/**
	 * Return document info
	 */
	public static function getInfo($id, $fields = '*') {
		return Query::factory('select')
			->table('tbl_documents')
			->fields($fields)
			->where("status_id IN (1,2)")
			->where('id=%d', $id)
			->limit(1)
			->execute()
			->row();
	}

	/**
	 * Return category data
	 */
	public static function getData($id) {
		$info = self::getInfo($id, 'schema_id');
		$schema = Schema::getInfo($info['schema_id'], 'file');
		return new Data($schema['file'], 'tbl_documents_fields', 'document_id', $id);
	}

	/**
	 * Return list of documents
	 */
	public static function getList($category_id = 0, $options = array()) {
		$schema_id = 0;
		if (isset($options['schema_id'])) {
			if (is_array($options['schema_id'])) {
				$schema_id = array();
				foreach ($options['schema_id'] as $item) {
					$schema_id[] = intval($item);
				}
			} else {
				$schema_id = intval($options['schema_id']);
			}
		}

		$status_id = 1;
		if (isset($options['status_id'])) {
			if (is_array($options['status_id'])) {
				$status_id = array();
				foreach ($options['status_id'] as $item) {
					$status_id[] = intval($item);
				}
			} else {
				$status_id = intval($options['status_id']);
			}
		}

		$page_id = 0;
		if (isset($options['page_id'])) {
			$page_id = intval($options['page_id']);
		}
		
		$page_size = 10;
		if (isset($options['page_size'])) {
			$page_size = intval($options['page_size']);
		}
		
		$query = Query::factory('select')
			->table('tbl_documents', 'd')
			->fields('d.*');
			
		if (isset($options['id']) && (intval($options['id']) > 0)) {
			$query->where('d.id=%d', $options['id']);
		}

		if (isset($options['name']) && !empty($options['name'])) {
			$query->where("d.name LIKE '%%%s%%'", $options['name']);
		}

		if (isset($options['alias']) && !empty($options['alias'])) {
			$query->where("d.alias LIKE '%%%s%%'", $options['alias']);
		}

		// Schema
		if (is_array($schema_id)) {
			$schema_str = '';
			foreach ($schema_id as $item) {
				if (!empty($schema_str)) {
					$schema_str .= ',';
				}
				$schema_str .= intval($item);
			}
			$query->where("(d.schema_id IN (%s))", $schema_str);
		} else if ($schema_id > 0) {
			$query->where("(d.schema_id=%d)", $schema_id);
		}
		
		// Status
		if (is_array($status_id)) {
			$status_str = '';
			foreach ($status_id as $item) {
				if (!empty($status_str)) {
					$status_str .= ',';
				}
				$status_str .= intval($item);
			}
			$query->where("(d.status_id IN (%s))", $status_str);
		} else if ($status_id > 0) {
			$query->where("(d.status_id=%d)", $status_id);
		} else {
			$query->where("(d.status_id IN (1,2))");
		}

		if ($category_id > 0) {
			$query->where('d2c.category_id=%d', $category_id)
				->join('tbl_document2category', 'd2c')
				->on('d.id=d2c.document_id')
				->orderBy('d2c.sequence', Query::ORDER_ASC);
			
		}
		$query->orderBy('d.id', Query::ORDER_ASC);

		if ($page_id > 0) {
			$query->limit($page_size);
			if ($page_id > 1) {
				$query->offset(($page_id - 1) * $page_size);
			}
		}

		return $query->execute()->rows();
	}
	
	/**
	 * Return number of documents
	 */
	public static function getCount($category_id = 0, $options = array()) {
		$schema_id = 0;
		if (isset($options['schema_id'])) {
			if (is_array($options['schema_id'])) {
				$schema_id = array();
				foreach ($options['schema_id'] as $item) {
					$schema_id[] = intval($item);
				}
			} else {
				$schema_id = intval($options['schema_id']);
			}
		}

		$status_id = 1;
		if (isset($options['status_id'])) {
			if (is_array($options['status_id'])) {
				$status_id = array();
				foreach ($options['status_id'] as $item) {
					$status_id[] = intval($item);
				}
			} else {
				$status_id = intval($options['status_id']);
			}
		}

		$query = Query::factory('select')
			->table('tbl_documents', 'd')
			->fields('COUNT(d.id) AS cnt');
			
		if (isset($options['id']) && (intval($options['id']) > 0)) {
			$query->where('d.id=%d', $options['id']);
		}

		if (isset($options['name']) && !empty($options['name'])) {
			$query->where("d.name LIKE '%%%s%%'", $options['name']);
		}

		if (isset($options['alias']) && !empty($options['alias'])) {
			$query->where("d.alias LIKE '%%%s%%'", $options['alias']);
		}

		// Schema
		if (is_array($schema_id)) {
			$schema_str = '';
			foreach ($schema_id as $item) {
				if (!empty($schema_str)) {
					$schema_str .= ',';
				}
				$schema_str .= intval($item);
			}
			$query->where("(d.schema_id IN (%s))", $schema_str);
		} else if ($schema_id > 0) {
			$query->where("(d.schema_id=%d)", $schema_id);
		}
		
		// Status
		if (is_array($status_id)) {
			$status_str = '';
			foreach ($status_id as $item) {
				if (!empty($status_str)) {
					$status_str .= ',';
				}
				$status_str .= intval($item);
			}
			$query->where("(d.status_id IN (%s))", $status_str);
		} else if ($status_id > 0) {
			$query->where("(d.status_id=%d)", $status_id);
		} else {
			$query->where("(d.status_id IN (1,2))", $status_id);
		}

		if ($category_id > 0) {
			$query->where('d2c.category_id=%d', $category_id)
				->join('tbl_document2category', 'd2c')
				->on('d.id=d2c.document_id');
		}

		$result = $query->execute()->row();

		return intval($result['cnt']);
	}

	/**
	 * Return document id by alias
	 */
	public static function getIdByAlias($alias, $exclude_id = 0) {
		$result = 0;

		if (is_int($alias) || ctype_digit($alias)) {
			$result = intval($alias);
		} else {
			$query = Query::factory('select')
				->table('tbl_documents')
				->fields('id')
				->where("status_id IN (1,2)")
				->where("alias='%s'", Database::escape($alias))
				->limit(1);
			
			if ($exclude_id > 0) {
				$query->where('id<>%d', $exclude_id);
			}

			$document = $query->execute()->row();
			if ($document) {
				$result = intval($document['id']);
			}
		}

		return $result;
	}

	/**
	 * Return document info
	 */
	public static function inCategories($id) {
		$result = array();
		
		$rows = Query::factory('select')
			->table('tbl_document2category')
			->fields('category_id')
			->where('document_id=%d', $id)
			->execute()
			->rows();
		
		foreach ($rows as $row) {
			$result[] = intval($row['category_id']);
		}
		
		return $result;
	}

	/**
	 * Insert document
	 */
	public static function insert($data) {
		return Query::factory('insert')
			->table('tbl_documents')
			->data($data)
			->execute()
			->getLastId();			
	}

	/**
	 * Update document
	 */
	public static function update($id, $data) {
		$query = Query::factory('update')
			->table('tbl_documents')
			->data($data)
			->where('id=%d', $id)
			->execute();
		$result = ($query->getAffectedRows() > -1);	
		
		if ($result) {
			$doc_info = self::getInfo($id, 'schema_id');
			$schema_info = Schema::getInfo($doc_info['schema_id'], 'file');
			Schema::saveFields($schema_info['file'], 'tbl_documents_fields', 'document_id', $id);
		}
		
		return $result;
	}

	/**
	 * Delete document
	 */
	public static function delete($id, $data) {
		$result = false;

		$query = Query::factory('update')
			->table('tbl_documents')
			->data($data)
			->where('id=%d', $id)
			->execute();
		if ($query->getAffectedRows() > -1) {
			$result = true;
		}
		
		if ($result) {
			$cat_list = self::inCategories($id);
			if (count($cat_list) > 0) {
				foreach ($cat_list as $cat) {
					$data = array();
					Query::data($data, 'sequence', 0, Query::DATA_INTEGER);
					Query::factory('update')
						->table('tbl_document2category')
						->data($data)
						->where('document_id=%d', $id)
						->where('category_id=%d', $cat)
						->execute();
					self::fixSequence($cat);
				}
			}
		}
		
		return $result;
	}

	/**
	 * Add document to category
	 */
	public static function addToCategory($doc_id, $cat_id) {
		$result = false;
		
		if (($doc_id > 0) && ($cat_id > 0) && !self::inCategory($doc_id, $cat_id)) {
			$data = array();
			Query::data($data, 'document_id', $doc_id, Query::DATA_INTEGER);
			Query::data($data, 'category_id', $cat_id, Query::DATA_INTEGER);
			$id = Query::factory('insert')
				->table('tbl_document2category')
				->data($data)
				->execute()
				->getLastId();
			
			if ($id) {
				self::fixSequence($cat_id);
				$result = true;
			}
		}
		
		return $result;
	}

	/**
	 * Remove document from category
	 */
	public static function removeFromCategory($doc_id, $cat_id) {
		$result = false;
		
		if (($doc_id > 0) && ($cat_id > 0) && self::inCategory($doc_id, $cat_id)) {
			$query = Query::factory('delete')
				->table('tbl_document2category')
				->where('document_id=%d', $doc_id)
				->where('category_id=%d', $cat_id)
				->execute();
					
			if (!$query->isError()) {
				self::fixSequence($cat_id);
				$result = true;
			}
		}
		
		return $result;
	}

	/**
	 * Is document in category
	 */
	public static function inCategory($doc_id, $cat_id) {
		$result = false;
		
		$doc_id = intval($doc_id);
		$cat_id = intval($cat_id);
		
		if (($doc_id > 0) && ($cat_id > 0)) {
			$query = Query::factory('select')
				->table('tbl_document2category')
				->where('document_id=%d', $doc_id)
				->where('category_id=%d', $cat_id)
				->limit(1)
				->execute();
			if ($query->row()) {
				$result = true;
			}
		}

		return $result;
	}

	public static function fixSequence($id) {
		$list = Query::factory('select')
			->table('tbl_document2category', 'd2c')
			->fields('d2c.id')
			->join('tbl_documents', 'd', Query::JOIN_INNER)
			->on('d2c.document_id=d.id')
			->where('d2c.category_id=%d', $id)
			->where('d.status_id IN (1,2)')
			->orderBy('d2c.sequence', Query::ORDER_ASC)
			->orderBy('d.id', Query::ORDER_ASC)
			->execute()
			->rows();

		$sequence = 1;
		foreach ($list as $item) {
			$data = array();
			Query::data($data, 'sequence', $sequence, Query::DATA_INTEGER);
			Query::factory('update')
				->table('tbl_document2category')
				->data($data)
				->where('id=%d', $item['id'])
				->execute();
			$sequence++;
		}
	}

	/**
	 * Move document up in sequence
	 */
	public static function moveUp($doc_id, $cat_id) {
		$error = 0;
		
		$source = Query::factory('select')
			->table('tbl_document2category')
			->fields('id,sequence')
			->where('category_id=%d', $cat_id)
			->where('document_id=%d', $doc_id)
			->limit(1)
			->execute()
			->row();
		
		if ($source) {
			$destination = Query::factory('select')
				->table('tbl_document2category')
				->fields('id,sequence')
				->where('category_id=%d', $cat_id)
				->where('(sequence<%d) AND (sequence>0)', $source['sequence'])
				->orderBy('sequence', Query::ORDER_DESC)
				->limit(1)
				->execute()
				->row();

			if ($destination) {
				$data = array();
				Query::data($data, 'sequence', $source['sequence'], Query::DATA_INTEGER);
				$query = Query::factory('update')
					->table('tbl_document2category')
					->data($data)
					->where('id=%d', $destination['id'])
					->execute();
				if ($query->isError()) {
					$error = 3;
				}

				if (!$error) {
					$data = array();
					Query::data($data, 'sequence', $destination['sequence'], Query::DATA_INTEGER);
					$query = Query::factory('update')
						->table('tbl_document2category')
						->data($data)
						->where('id=%d', $source['id'])
						->execute();
					if ($query->isError()) {
						$error = 4;
					}
				}
			} else {
				$error = 2;
			}
		} else {
			$error = 1;
		}
		
		return $error;
	}

	/**
	 * Move document down in sequence
	 */
	public static function moveDown($doc_id, $cat_id) {
		$error = 0;

		$source = Query::factory('select')
			->table('tbl_document2category')
			->fields('id,sequence')
			->where('category_id=%d', $cat_id)
			->where('document_id=%d', $doc_id)
			->limit(1)
			->execute()
			->row();

		if ($source) {
			$destination = Query::factory('select')
				->table('tbl_document2category')
				->fields('id,sequence')
				->where('category_id=%d', $cat_id)
				->where('(sequence>%d) AND (sequence>0)', $source['sequence'])
				->orderBy('sequence', Query::ORDER_ASC)
				->limit(1)
				->execute()
				->row();

			if ($destination) {
				$data = array();
				Query::data($data, 'sequence', $source['sequence'], Query::DATA_INTEGER);
				$query = Query::factory('update')
					->table('tbl_document2category')
					->data($data)
					->where('id=%d', $destination['id'])
					->execute();
				if ($query->isError()) {
					$error = 3;
				}

				if (!$error) {
					$data = array();
					Query::data($data, 'sequence', $destination['sequence'], Query::DATA_INTEGER);
					$query = Query::factory('update')
						->table('tbl_document2category')
						->data($data)
						->where('id=%d', $source['id'])
						->execute();
					if ($query->isError()) {
						$error = 4;
					}
				}
			} else {
				$error = 2;
			}
		} else {
			$error = 1;
		}
		
		return $error;
	}
}
